﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace RecruitStudents.Utility.Helper
{

    public class ExcelHelper
    {

        #region NPOI
        IWorkbook wb;
        public ExcelHelper(string fileDirectory)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.
            using (FileStream file = new FileStream(fileDirectory, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);

            }
        }

        public DataTable ReadExcelToDataTable()
        {
            ISheet sheet = wb.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            IRow temp = sheet.GetRow(0);
            for (int j = 0; j < temp.PhysicalNumberOfCells; j++)
            {
                ICell tmepcell = temp.GetCell(j);
                if (tmepcell == null)
                {
                    dt.Columns.Add("未指定列名");
                }
                else
                {
                    dt.Columns.Add(tmepcell.ToString());
                }

            }
            while (rows.MoveNext())
            {

                IRow row = (IRow)rows.Current;
                if (row.RowNum == 0)
                {
                    continue;
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {

                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        #endregion

        //NPOI简单Demo   NPOI认为Excel的第一个单元格是：(0，0)
        public static void ExportEasy(DataTable dtSource, string strFileName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

            //填充表头
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }

            //填充内容
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }

            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
    }

    //直接Excel生成操作类
    public class NPOIHelper
    {
        #region 生成Excel

        public DataTable ReadExcelToDataTable(IWorkbook wb)
        {
            ISheet sheet = wb.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            IRow temp = sheet.GetRow(0);
            for (int j = 0; j < temp.PhysicalNumberOfCells; j++)
            {
                ICell tmepcell = temp.GetCell(j);
                if (tmepcell == null)
                {
                    dt.Columns.Add("未指定列名");
                }
                else
                {
                    dt.Columns.Add(tmepcell.ToString());
                }

            }
            while (rows.MoveNext())
            {

                IRow row = (IRow)rows.Current;
                if (row.RowNum == 0)
                {
                    continue;
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {

                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        #endregion

        #region 导出Excel
        /// <summary>
        /// 导出列名
        /// </summary>
        public static System.Collections.SortedList ListColumnsName;
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static void ExportExcel(DataTable dtSource, string filePath, string title)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook, title);
            SaveExcelFile(excelWorkbook, filePath);
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static void ExportExcel(DataTable dtSource, Stream excelStream, string title)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook, title);
            SaveExcelFile(excelWorkbook, excelStream);
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(filePath, FileMode.Create);
                excelWorkBook.Write(file);

            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {

            }
        }
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        protected static HSSFWorkbook CreateExcelFile()
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            return hssfworkbook;
        }
        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        protected static void CreateHeader(ISheet excelSheet)
        {
            int cellIndex = 0;
            IRow newRow = excelSheet.CreateRow(0);
            ICell newCell = null;
            //循环导出列
            foreach (System.Collections.DictionaryEntry de in ListColumnsName)
            {

                newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                cellIndex++;
            }
        }
        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        protected static void CreateHeader(ISheet excelSheet, IWorkbook excelWorkbook, string title)
        {
            IRow headerRow = excelSheet.CreateRow(0);
            ICell headerCell = headerRow.CreateCell(0);

            headerCell.SetCellValue(title);

            ICellStyle style = excelWorkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;

            //create a font style  
            IFont font = excelWorkbook.CreateFont();
            font.FontHeight = 18 * 18;
            font.Boldweight = 700;
            style.SetFont(font);
            headerCell.CellStyle = style;


            //merged cells on single row  
            //ATTENTION: don't use Region class, which is obsolete  
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, ListColumnsName.Count - 1);
            excelSheet.AddMergedRegion(region);
            //set enclosed border for the merged region  
            ((HSSFSheet)excelSheet).SetEnclosedBorderOfRegion(region, BorderStyle.MEDIUM, NPOI.HSSF.Util.HSSFColor.BLACK.index);

            IRow datetimeRow = excelSheet.CreateRow(1);
            ICell datetimeCell = datetimeRow.CreateCell(0);

            datetimeCell.SetCellValue("导出时间：" + DateTime.Now.ToString("yyyy-mm-dd hh:mm:ss"));

            ICellStyle datetimestyle = excelWorkbook.CreateCellStyle();
            datetimestyle.Alignment = HorizontalAlignment.LEFT;
            datetimestyle.BorderRight = BorderStyle.THIN;
            datetimestyle.BorderLeft = BorderStyle.THIN;
            //create a font style  
            datetimeCell.CellStyle = datetimestyle;


            //merged cells on single row  
            //ATTENTION: don't use Region class, which is obsolete  
            CellRangeAddress datetimeregion = new CellRangeAddress(1, 1, 0, ListColumnsName.Count - 1);
            excelSheet.AddMergedRegion(datetimeregion);




            int cellIndex = 0;
            IRow newRow = excelSheet.CreateRow(2);
            ICell newCell = null;
            //循环导出列
            foreach (System.Collections.DictionaryEntry de in ListColumnsName)
            {
                ICellStyle cellStyle = excelWorkbook.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                IFont cellfont = excelWorkbook.CreateFont();
                cellfont.FontHeight = 15 * 15;
                cellfont.Boldweight = 700;
                cellStyle.SetFont(cellfont);
                cellStyle.WrapText = true;
                cellStyle.BorderTop = BorderStyle.THIN;
                cellStyle.BorderLeft = BorderStyle.THIN;
                cellStyle.BorderRight = BorderStyle.THIN;
                cellStyle.BorderBottom = BorderStyle.THIN;
                newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                newCell.CellStyle = cellStyle;

                cellIndex++;

            }

            for (int i = 0; i < ListColumnsName.Count; i++)
            {
                excelSheet.SetColumnWidth(i, 6000);
            }

        }
        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, IWorkbook excelWorkbook, string title)
        {
            int rowCount = 2;
            int sheetCount = 1;
            ISheet newsheet = null;


            ICellStyle cellallStyle = excelWorkbook.CreateCellStyle();
            cellallStyle.WrapText = true;
            cellallStyle.BorderTop = BorderStyle.THIN;
            cellallStyle.BorderLeft = BorderStyle.THIN;
            cellallStyle.BorderRight = BorderStyle.THIN;
            cellallStyle.BorderBottom = BorderStyle.THIN;
            cellallStyle.Alignment = HorizontalAlignment.CENTER;
            cellallStyle.VerticalAlignment = VerticalAlignment.CENTER;



            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
            CreateHeader(newsheet, excelWorkbook, title);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出10000条数据 创建新的工作簿
                if (rowCount % 40000 == 0)
                {
                    rowCount = 2;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(newsheet, excelWorkbook, title);
                }

                IRow newRow = newsheet.CreateRow(rowCount);
                InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, cellallStyle);
            }
        }
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook, ICellStyle cellallStyle)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                ICell newCell = null;
                string drValue = drSource[columnsName].ToString().Trim();
                System.Type rowType = drSource[columnsName].GetType();
                cellallStyle.DataFormat = -1;
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        newCell.CellStyle = cellallStyle;
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);


                        IDataFormat format = excelWorkBook.CreateDataFormat();
                        cellallStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");

                        newCell.CellStyle = cellallStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.CellStyle = cellallStyle;
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                        int int32V = 0;
                        int.TryParse(drValue, out int32V);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(int32V);
                        newCell.CellStyle = cellallStyle;
                        break;
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        newCell.CellStyle = cellallStyle;
                        break;
                    case "System.Decimal"://浮点型
                        double decimalV = 0;
                        double.TryParse(drValue, out decimalV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(decimalV);
                        newCell.CellStyle = cellallStyle;
                        break;
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        newCell.CellStyle = cellallStyle;
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        newCell.CellStyle = cellallStyle;
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }
        #endregion

        public static void ExportExcelForDtByTemplate(DataTable dtSource, string strTemplateFileName, string strSavePath, int rowIndex)
        {
            int dtRowIndex = dtSource.Rows.Count;       // DataTable的数据行数  
            FileStream file = new FileStream(strTemplateFileName, FileMode.Open, FileAccess.Read);//读入excel模板  
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            ISheet sheet = workbook.GetSheetAt(0);

            ICellStyle cellallStyle = workbook.CreateCellStyle();
            cellallStyle.WrapText = true;
            cellallStyle.BorderTop = BorderStyle.THIN;
            cellallStyle.BorderLeft = BorderStyle.THIN;
            cellallStyle.BorderRight = BorderStyle.THIN;
            cellallStyle.BorderBottom = BorderStyle.THIN;
            cellallStyle.Alignment = HorizontalAlignment.CENTER;
            cellallStyle.VerticalAlignment = VerticalAlignment.CENTER;
            cellallStyle.DataFormat = -1;


            foreach (DataRow row in dtSource.Rows)
            {
                #region 填充内容
                //IRow dataRow = sheet.GetRow(rowIndex);
                IRow dataRow = sheet.CreateRow(rowIndex);
                int columnIndex = 0;        // 开始列（0为标题列，从1开始）  
                foreach (DataColumn column in dtSource.Columns)
                {
                    // 列序号赋值  
                    if (columnIndex >= dtSource.Columns.Count)
                        break;

                    ICell newCell = dataRow.GetCell(columnIndex);
                    if (newCell == null)
                        newCell = dataRow.CreateCell(columnIndex);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            drValue = drValue.Replace("&", "&");
                            drValue = drValue.Replace(">", ">");
                            drValue = drValue.Replace("<", "<");
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            IDataFormat format = workbook.CreateDataFormat();
                            cellallStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.CellStyle = cellallStyle;
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                            int int32V = 0;
                            int.TryParse(drValue, out int32V);
                            newCell.SetCellValue(int32V);
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV.ToString());
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.Decimal"://浮点型
                            double decimalV = 0;
                            double.TryParse(drValue, out decimalV);
                            newCell.SetCellValue(decimalV);
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellallStyle;
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellallStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellallStyle;
                            break;
                    }
                    columnIndex++;
                }
                #endregion

                rowIndex++;
            }
            SaveExcelFile(workbook, strSavePath);
        }

        public static void ExportExcelForDtByTemplate_New(DataTable dtSource, string strTemplateFileName, string strSavePath, string strTitle,
            string strTitle2, string strColuName)
        {

            int dtRowIndex = dtSource.Rows.Count;       // DataTable的数据行数  
            FileStream file = new FileStream(strTemplateFileName, FileMode.Open, FileAccess.Read);//读入excel模板  
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            workbook.SetSheetName(0, dtSource.TableName);
            ISheet sheet = workbook.GetSheetAt(0);

            ICellStyle cellallStyle = workbook.CreateCellStyle();
            cellallStyle.WrapText = true;
            cellallStyle.BorderTop = BorderStyle.THIN;
            cellallStyle.BorderLeft = BorderStyle.THIN;
            cellallStyle.BorderRight = BorderStyle.THIN;
            cellallStyle.BorderBottom = BorderStyle.THIN;
            cellallStyle.Alignment = HorizontalAlignment.CENTER;
            cellallStyle.VerticalAlignment = VerticalAlignment.CENTER;
            cellallStyle.DataFormat = -1;

            IRow dataRow_Title = sheet.GetRow(0);
            ICell newCell_Title = dataRow_Title.GetCell(0);
            if (newCell_Title == null)
            {
                newCell_Title = dataRow_Title.CreateCell(0);
            }
            newCell_Title.SetCellValue(strTitle);

            IRow dataRow_Title2 = sheet.GetRow(1);
            ICell newCell_Title2 = dataRow_Title2.GetCell(0);
            if (newCell_Title2 == null)
            {
                newCell_Title2 = dataRow_Title2.CreateCell(0);
            }
            newCell_Title2.SetCellValue(strTitle2);



            int rowIndex = 3;
            int columnIndex = 0;        // 开始列（0为标题列，从1开始） 
            IRow dataRow = null;
            for (int i = 1; i <= dtSource.Rows.Count; i++)
            {
                if ((i % 2) == 0)
                {
                    columnIndex = 7;
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        ICell newCell = dataRow.GetCell(columnIndex);
                        if (newCell == null)
                            newCell = dataRow.CreateCell(columnIndex);
                        newCell.CellStyle = cellallStyle;
                        newCell.SetCellValue(dtSource.Rows[i - 1][column].ToSafeString());
                        columnIndex++;
                    }
                    columnIndex = 0;
                    rowIndex++;
                }
                else
                {
                    dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        ICell newCell = dataRow.GetCell(columnIndex);
                        if (newCell == null)
                            newCell = dataRow.CreateCell(columnIndex);
                        newCell.CellStyle = cellallStyle;
                        newCell.SetCellValue(dtSource.Rows[i - 1][column].ToSafeString());
                        columnIndex++;
                    }
                }
            }
            workbook.GetSheetAt(0).AutoSizeColumn(0);
            workbook.GetSheetAt(0).AutoSizeColumn(1);
            workbook.GetSheetAt(0).AutoSizeColumn(2);
            workbook.GetSheetAt(0).AutoSizeColumn(3);
            workbook.GetSheetAt(0).AutoSizeColumn(4);
            workbook.GetSheetAt(0).AutoSizeColumn(5);


            workbook.GetSheetAt(0).AutoSizeColumn(7);
            workbook.GetSheetAt(0).AutoSizeColumn(8);
            workbook.GetSheetAt(0).AutoSizeColumn(9);
            workbook.GetSheetAt(0).AutoSizeColumn(10);
            workbook.GetSheetAt(0).AutoSizeColumn(11);
            workbook.GetSheetAt(0).AutoSizeColumn(12);
            SaveExcelFile(workbook, strSavePath);
        }


        public static void ExportExcelForDtByTemplate_New(DataTable dtSource, string strTemplateFileName, string strSavePath, string strTitle, string strTitle2)
        {
            ExportExcelForDtByTemplate_New(dtSource, strTemplateFileName, strSavePath, strTitle, strTitle2, string.Empty);
        }

        #region 根据DataSet导出Excel
        public static void ExportExcel(DataSet ds, string filePath)
        {
            HSSFWorkbook workBook = new HSSFWorkbook();

            ICellStyle cellallStyle = workBook.CreateCellStyle();
            cellallStyle.WrapText = true;
            cellallStyle.BorderTop = BorderStyle.THIN;
            cellallStyle.BorderLeft = BorderStyle.THIN;
            cellallStyle.BorderRight = BorderStyle.THIN;
            cellallStyle.BorderBottom = BorderStyle.THIN;
            cellallStyle.Alignment = HorizontalAlignment.CENTER;
            cellallStyle.VerticalAlignment = VerticalAlignment.CENTER;


            foreach (DataTable dt in ds.Tables)
            {
                ISheet sheet = createSheet(workBook, dt.TableName, dt, cellallStyle);
            }

            SaveExcelFile(workBook, filePath);

        }

        private static ISheet createSheet(HSSFWorkbook workBook, string sheetName, DataTable dt, ICellStyle cellallStyle)
        {
            ISheet sheet = workBook.CreateSheet(sheetName);
            IRow RowHead = sheet.CreateRow(0);
            for (int iColumnIndex = 0; iColumnIndex < dt.Columns.Count; iColumnIndex++)
            {
                ICell cell = RowHead.CreateCell(iColumnIndex);
                cell.CellStyle = cellallStyle;
                cell.SetCellValue(dt.Columns[iColumnIndex].Caption);
            }
            for (int iRowIndex = 0; iRowIndex < dt.Rows.Count; iRowIndex++)
            {
                IRow RowBody = sheet.CreateRow(iRowIndex + 1);
                for (int iColumnIndex = 0; iColumnIndex < dt.Columns.Count; iColumnIndex++)
                {
                    ICell cell = RowBody.CreateCell(iColumnIndex);
                    cell.CellStyle = cellallStyle;
                    cell.SetCellValue(dt.Rows[iRowIndex][iColumnIndex].ToSafeString());
                }
            }
            return sheet;
        }
        #endregion
    }

    //向指定的Excel模版文件中写数据
    public class NPOITemplateHelper
    {
        protected static IWorkbook workbook = null;
        protected static ISheet sheet = null;

        protected static void loadTemplate(String templateURL)
        {
            bool a = templateURL.Trim().IndexOf(".xls") == -1;
            bool b = templateURL.Trim().IndexOf(".XLS") == -1;
            bool c = templateURL.Trim().IndexOf(".xlsx") == -1;
            bool d = templateURL.Trim().IndexOf(".XLSX") == -1;
            if (!string.IsNullOrEmpty(templateURL) && (a || b || c || d))// && c&&d)  
            {

                using (FileStream file = new FileStream(templateURL, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file, ImportOption.All);
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {

            }
        }
    }

    //排序实现接口 不进行排序 根据添加顺序导出
    public class NoSort : System.Collections.IComparer
    {
        public int Compare(object x, object y)
        {
            return -1;
        }
    }

    public static class ExcelConvert
    {
        #region - 由数字转换为Excel中的列字母 -

        public static int ToIndex(string columnName)
        {
            if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) { throw new Exception("invalid parameter"); }
            int index = 0;
            char[] chars = columnName.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1;
        }

        public static string ToName(int index)
        {
            if (index < 0) { throw new Exception("invalid parameter"); }
            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return String.Join(string.Empty, chars.ToArray());
        }
        #endregion
    }
}
